from utils import log_info, log_exception, log_error, log_debug
from abc import ABC, abstractmethod
from typing import List, Dict, Any, Optional, Generic, TypeVar, Type, Union
import sqlite3

from utils.logger import log_warning
from .db_pool import get_db_pool

# 定义泛型类型变量
T = TypeVar('T')


class BaseDAO(ABC, Generic[T]):
    """DAO抽象基类，提供通用的数据库操作方法"""

    def __init__(self, entity_class: Type[T]):
        """
        初始化DAO
        :param entity_class: 实体类类型
        """
        self.db_pool = get_db_pool()
        self.table_name = self._get_table_name()
        self.primary_key = self._get_primary_key()
        self.entity_class = entity_class

    @abstractmethod
    def _get_table_name(self) -> str:
        """获取表名"""
        pass

    @abstractmethod
    def _get_primary_key(self) -> str:
        """获取主键字段名"""
        pass

    def _get_connection(self) -> Optional[sqlite3.Connection]:
        """获取数据库连接（从连接池）"""
        conn = self.db_pool.get_connection()
        if not conn:
            log_error(f"无法获取数据库连接，表名: {self.table_name}")
        return conn

    def _process_row(self, cursor, row) -> Dict[str, Any]:
        """处理查询结果行，转换为字典"""
        columns = [desc[0] for desc in cursor.description]
        return dict(zip(columns, row))

    def _dict_to_entity(self, data: Dict[str, Any]) -> T:
        """将字典转换为实体"""
        if hasattr(self.entity_class, 'from_dict'):
            return self.entity_class.from_dict(data)
        return self.entity_class(**data)

    def _execute_query(self, sql: str, params: tuple = None, single_result: bool = False) -> Union[Optional[T], List[T]]:
        """
        执行SQL查询并转换结果为实体
        :param sql: SQL查询语句
        :param params: 查询参数
        :param single_result: 是否只返回单个结果
        :return: 实体对象或实体对象列表
        """
        conn = None
        try:
            conn = self._get_connection()
            if not conn:
                return None if single_result else []
            cursor = conn.cursor()
            cursor.execute(sql, params or ())

            if single_result:
                row = cursor.fetchone()
                if row:
                    data = self._process_row(cursor, row)
                    # print(f"查询结果类型: {type(data)}")
                    result = self._dict_to_entity(data)
                    # print(f"转换后的实体类型: {type(result)}")
                    return result
                return None
            else:
                rows = cursor.fetchall()
                entities = []
                for row in rows:
                    data = self._process_row(cursor, row)
                    # print(f"查询结果类型: {type(data)}")
                    result_row = self._dict_to_entity(data)
                    # print(f"转换后的实体类型: {type(result_row)}")
                    entities.append(result_row)
                return entities
        except Exception as e:
            log_exception(f"执行查询失败: {str(e)}")
            return None if single_result else []
        finally:
            if conn:
                self.db_pool.release_connection(conn)

    def create(self, entity: T) -> Optional[int]:
        """
        创建新记录
        :param entity: 实体对象
        :return: 创建成功返回记录ID，否则返回None
        """
        try:
            # 获取实体属性字典
            if hasattr(entity, '__dict__') and callable(entity.__dict__):
                entity_dict = entity.__dict__()
            else:
                entity_dict = entity.__dict__
            # 过滤掉None值和主键（如果已存在）
            filtered_dict = {k: v for k, v in entity_dict.items() 
                            if v is not None and k != self.primary_key}

            # 构建插入语句
            fields = ', '.join(filtered_dict.keys())
            placeholders = ', '.join(['?' for _ in filtered_dict.keys()])
            values = list(filtered_dict.values())

            conn = self._get_connection()
            if not conn:
                return None
            cursor = conn.cursor()
            cursor.execute(f"INSERT INTO {self.table_name} ({fields}) VALUES ({placeholders})", values)
            conn.commit()
            record_id = cursor.lastrowid
            log_debug(f"创建{self.table_name}记录成功，ID: {record_id}")
            return record_id
        except Exception as e:
            log_exception(f"创建{self.table_name}记录失败: {str(e)}")
            return None
        finally:
            if 'conn' in locals():
                self.db_pool.release_connection(conn)

    def get_by_id(self, record_id: int) -> Optional[T]:
        """
        根据ID获取记录
        :param record_id: 记录ID
        :return: 实体对象，如果不存在返回None
        """
        sql = f"SELECT * FROM {self.table_name} WHERE {self.primary_key} = ?"
        params = (record_id,)
        return self._execute_query(sql, params, single_result=True)

    def update(self, entity: T) -> bool:
        """
        更新记录
        :param entity: 实体对象（必须包含主键）
        :return: 更新成功返回True，否则返回False
        """
        try:
            # 获取实体属性字典
            if hasattr(entity, '__dict__') and callable(entity.__dict__):
                entity_dict = entity.__dict__()
            else:
                entity_dict = entity.__dict__
            # 获取主键值
            record_id = entity_dict.get(self.primary_key)
            if record_id is None:
                log_error(f"实体缺少主键: {self.primary_key}")
                return False

            # 过滤掉None值和主键
            filtered_dict = {k: v for k, v in entity_dict.items() 
                            if v is not None and k != self.primary_key}

            # 构建更新语句
            set_clause = ', '.join([f"{k} = ?" for k in filtered_dict.keys()])
            values = list(filtered_dict.values()) + [record_id]

            conn = self._get_connection()
            if not conn:
                return False
            cursor = conn.cursor()
            cursor.execute(f"UPDATE {self.table_name} SET {set_clause} WHERE {self.primary_key} = ?", values)
            conn.commit()
            log_debug(f"更新{self.table_name}记录成功，ID: {record_id}")
            return True
        except Exception as e:
            log_exception(f"更新{self.table_name}记录失败: {str(e)}")
            return False
        finally:
            if 'conn' in locals():
                self.db_pool.release_connection(conn)

    def delete(self, record_id: int) -> bool:
        """
        删除记录
        :param record_id: 记录ID
        :return: 删除成功返回True，否则返回False
        """
        try:
            conn = self._get_connection()
            if not conn:
                return False
            cursor = conn.cursor()
            cursor.execute(f"DELETE FROM {self.table_name} WHERE {self.primary_key} = ?", (record_id,))
            conn.commit()
            if cursor.rowcount > 0:
                log_debug(f"删除{self.table_name}记录成功: {record_id}")
                return True
            log_warning(f"未找到{self.table_name}记录ID: {record_id}，删除失败")
            return False
        except Exception as e:
            log_exception(f"删除{self.table_name}记录失败: {str(e)}")
            return False
        finally:
            if 'conn' in locals():
                self.db_pool.release_connection(conn)

    def get_all(self) -> List[T]:
        """
        获取所有记录
        :return: 实体对象列表
        """
        sql = f"SELECT * FROM {self.table_name}"
        return self._execute_query(sql)